package com.aquila.bible.sqlite;

import java.util.ArrayList;

import com.aquila.bible.ReadHistoryObject;
import com.aquila.bible.SearchResultObject;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.Toast;

public class CollectionSqlite extends SQLiteOpenHelper {
	private static final String DATABASE_NAME = "CollectionSqlite.db";
	public static final String TABLE_NAME = "CollectionSqlite_TB_NAME";
	public static final int Version =1;
	private SQLiteDatabase db;
	
	private final String KEY_ID = "id" ;
	private final String KEY_KEY_WORDS= "key_words" ;
	private final String KEY_BOOK_STRING = "book_string" ;
	private final String KEY_BOOK_SHORT_STRING = "book_short_string" ;
	private final String KEY_BOOK_INDEX = "book_index" ;
	private final String KEY_CHAPTER_INDEX = "chapter_index" ;
	private final String KEY_CHAPTER_COUNT = "chapter_count" ;
	private final String KEY_CONTENT_STRING = "content_string" ;
	private final String KEY_SECTION_INDEX = "section_index" ;
	private final String KEY_SECTION_COUNT = "section_count" ;
	private final String KEY_ORDER_TIME = "order_time" ;
	
	/***
	 * 创建数据库表
	 * @param db
	 *2012-7-30 下午1:15:53
	 */
	private void createTable(SQLiteDatabase db){
		String sqlstr = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " ( "+
				KEY_ID +" INTEGER PRIMARY KEY, "+
	            KEY_KEY_WORDS + "  VARCHAR2(50), "+
	            KEY_BOOK_STRING + "  VARCHAR2(30), "+
	            KEY_BOOK_SHORT_STRING+ "  VARCHAR2(30), "+
	            KEY_BOOK_INDEX+ "  INTEGER, "+
	            KEY_CHAPTER_INDEX+ "  INTEGER, "+
	            KEY_CHAPTER_COUNT+ "  INTEGER, "+
	            KEY_ORDER_TIME+ "  VARCHAR2(30), "+
	            KEY_CONTENT_STRING +" TEXT, "+
	            KEY_SECTION_INDEX + " INTEGER, " +
	            KEY_SECTION_COUNT + " INTEGER);";
		try{
			db.execSQL(sqlstr);
		}catch (SQLException e) {
			e.printStackTrace() ;
		}
	}
	private Context context ;
	public CollectionSqlite(Context context) {
		super(context, DATABASE_NAME, null, Version);
		this.context = context ;
		db = getWritableDatabase() ;
		createTable(db) ;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		
	}
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
		onCreate(db);
	}
	
	/***
	 * 打开数据库
	 *2012-7-30 下午1:37:13
	 */
	public void open() {
		if (db == null || db.isOpen()) {
			db = getWritableDatabase() ;
		}
	}
	
	/**
	 * 关闭数据库
	 */
	public void close(){
		if (db.isOpen()) {
			db.close() ;
		}
	}
	
	
	/**
	 * 加载数据库所有记录
	 * @return
	 *2012-8-8 上午10:12:42
	 */
	public ArrayList<SearchResultObject> getAllData(){
		ArrayList<SearchResultObject> arrayList = new ArrayList<SearchResultObject>() ;
		String sql = "select * from " + TABLE_NAME ;
		Cursor cursor = db.rawQuery(sql, null) ;
		if (cursor.getCount() == 0 ) {
        	return null;
        }
		cursor.moveToFirst() ;
		do {
			arrayList.add(readDataResultObject(cursor)) ;
        } while(cursor.moveToNext()) ;

		cursor.close() ;
		
		return arrayList ;
	}
	
	
	/**
	 * 插入一条数据
	 * @param object
	 *2012-8-8 上午10:09:36
	 */
	public boolean insertData(SearchResultObject object){
		if (object == null) {
			return false;
		}
		if (judegeExistData(object)) {
        	return  false;
        }
			ContentValues contentValues = inputContentValues(object) ;
			db.insert(TABLE_NAME, null, contentValues) ;
			return true;
	}
	
	/**
	 * 判断是否数据库是否已存在
	 * @param object
	 * @return
	 *Administrator
	 *2012-8-9 下午9:19:22
	 */
	public boolean isExistData(SearchResultObject object){
		String sql = "select * from " + TABLE_NAME + "where " 
				+ KEY_CONTENT_STRING + " = " + object.getContentString() ;
//		"select * from "+TABLE_NAME+" where  orderSerialId =?"
		Cursor cursor = db.rawQuery(sql, null) ;
		if (cursor.getCount() == 0 ) {
			cursor.close() ;
        	return false ;
        }
		do {
        	SearchResultObject resultObject = readDataResultObject(cursor) ;
        	if (resultObject.getBookShortString().equals(object.getBookShortString())) {
        		cursor.close() ;
        		return true;
            }
        } while (cursor.moveToNext());
		
		cursor.close() ;
		return false ;
	}
	
	
	
	private boolean judegeExistData(SearchResultObject object){
		int len = getDataCount() ;
		ArrayList<SearchResultObject> arrayList = getAllData() ;
		SearchResultObject tempObject ;
		for (int i = 0; i < len; i++) {
		tempObject = arrayList.get(i) ;
        	if ((object.getContentString().equals(tempObject.getContentString())
        			&& object.getBookIndex() == tempObject.getBookIndex()
        			)
        			) {
            	return true ;
            }
        }
		return false ;
	}
	
	/**
	 * 删除数据
	 * @param object
	 *2012-8-8 上午11:46:28
	 */
	public void deleteData(SearchResultObject object){
		if (object == null) {
			return ;
		}
		
		if (object.getId() > 0) {
			db.delete(TABLE_NAME, KEY_ID + " = " +object.getId(), null) ;
		}
		else {
			Toast.makeText(context, "查无此数据", Toast.LENGTH_SHORT).show() ;	
		}
	}
	
	/**
	 * 更新一条数据
	 * @param object
	 *2012-8-8 上午11:44:15
	 */
	public void updateData(SearchResultObject object){
		if (object == null) {
			return ;
		}
		ContentValues contentValues = inputContentValues(object) ;
		db.update(TABLE_NAME, contentValues, KEY_ID +" = "+object.getId(), null) ;
		
		
		
	}
	
	
	/**
	 * 获取保存历史的记录
	 * @return
	 *2012-7-30 下午1:57:52
	 */
	public int getDataCount(){
		String sqlString = "select * from " + TABLE_NAME ;
		Cursor cursor = db.rawQuery(sqlString, null) ;
		return cursor.getCount() ;
	}
	
	
	/**
	 * 封装读取数据的方法
	 * @param cursor
	 * @return
	 *2012-8-8 上午10:00:08
	 */
	private SearchResultObject readDataResultObject(Cursor cursor){
		SearchResultObject object = new SearchResultObject() ;
		object.setId(cursor.getInt(cursor.getColumnIndex(KEY_ID))) ;
		object.setBookIndex(cursor.getInt(cursor.getColumnIndex(KEY_BOOK_INDEX))) ;
		object.setBookShortString(cursor.getString(cursor.getColumnIndex(KEY_BOOK_SHORT_STRING))) ;
		object.setBookString(cursor.getString(cursor.getColumnIndex(KEY_BOOK_STRING))) ;
		object.setChapterCount(cursor.getInt(cursor.getColumnIndex(KEY_CHAPTER_COUNT))) ;
		object.setChapterIndex(cursor.getInt(cursor.getColumnIndex(KEY_CHAPTER_INDEX))) ;
		object.setContentString(cursor.getString(cursor.getColumnIndex(KEY_CONTENT_STRING))) ;
		object.setKeyWords(cursor.getString(cursor.getColumnIndex(KEY_KEY_WORDS))) ;
		object.setSectionCount(cursor.getInt(cursor.getColumnIndex(KEY_SECTION_COUNT))) ;
		object.setSectionIndex(cursor.getInt(cursor.getColumnIndex(KEY_SECTION_INDEX))) ;
		object.setTimeString(cursor.getString(cursor.getColumnIndex(KEY_ORDER_TIME))) ;
		return object ;
	}
	
	
	/**
	 * 存数据封装的方法
	 * @param object
	 * @return
	 *2012-8-8 上午9:45:14
	 */
	private ContentValues inputContentValues(SearchResultObject object){
		ContentValues cv = new ContentValues() ;
		cv.put(KEY_KEY_WORDS, object.getKeyWords()) ;
		cv.put(KEY_BOOK_INDEX, object.getBookIndex()) ;
		cv.put(KEY_BOOK_SHORT_STRING, object.getBookShortString()) ;
		cv.put(KEY_BOOK_STRING, object.getBookString()) ;
		cv.put(KEY_CHAPTER_COUNT, object.getChapterCount()) ;
		cv.put(KEY_CHAPTER_INDEX, object.getChapterIndex()) ;
		cv.put(KEY_CONTENT_STRING, object.getContentString()) ;
		cv.put(KEY_SECTION_COUNT, object.getSectionCount()) ;
		cv.put(KEY_SECTION_INDEX, object.getSectionIndex()) ;
		cv.put(KEY_ORDER_TIME, object.getTimeString()) ;
		return cv ;
	}
	
	
}
